<?php

namespace App\Services;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * 导出类
 */
class Excel
{
    /**
     * 导出Excel
     *
     * @param $xlsTitle
     * @param $tableData
     * @param $cellName
     * @param $filePath
     * @param $fileName
     * @param bool $isDownload
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function exportExcel($xlsTitle, $tableData, $cellName, $filePath, $fileName, $isDownload = false)
    {
        set_time_limit(0);
        $spreadsheet = new Spreadsheet();
        $spreadsheet->setActiveSheetIndex(0);
        $spreadsheet->getActiveSheet()->setTitle($xlsTitle);

//        $cellArray0 = [
//            'A' => 'Title',
//            'B' => 'Desc',
//            'C' => 'Date',
//        ];
        $cellArray1 = $cellName;

        /*
        // 一级表头
        foreach ($cellArray0 as $key0 => $cell0) {
            //$sheet = $spreadsheet->getActiveSheet();
            //$sheet->setCellValue($key0 . '1', $cell0);
            $spreadsheet->getActiveSheet()->setCellValue($key0 . '1', $cell0);
            $spreadsheet->getActiveSheet()->getColumnDimension($key0)->setAutoSize(true);
            $spreadsheet->getActiveSheet()->getColumnDimension($key0)->setWidth(10);
            // Set Font
            $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
            $spreadsheet->getDefaultStyle()->getFont()->setName("Arial");
            $spreadsheet->getActiveSheet()->getStyle($key0 . '1')->getFont()->setBold(true);
        }
        $index0 = 2;
        $spreadsheet->getActiveSheet()->getRowDimension($index0)->setRowHeight(14.25);
        $spreadsheet->getActiveSheet()->mergeCells('A' . ($index0) . ':B' . ($index0));// 合并单元格
        $spreadsheet->getActiveSheet()->getStyle('A' . ($index0))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
        $spreadsheet->getActiveSheet()->setCellValueExplicit('A' . ($index0), 'TEST-' . rand(100001, 999999), DataType::TYPE_STRING);
        $spreadsheet->getActiveSheet()->getStyle('B' . ($index0))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
        $spreadsheet->getActiveSheet()->getStyle('B' . ($index0))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $spreadsheet->getActiveSheet()->setCellValue('C' . ($index0), date('y-m-d H:i:s'));
        */

        if ($tableData) {
            //$limit = ceil(count($tableData) / 1000);
            $index1 = 2; // $index1 = 5;
            foreach ($tableData as $k => $item) {
                $spreadsheet->getActiveSheet()->getRowDimension($index1)->setRowHeight(14.25);
                foreach ($cellArray1 as $key1 => $cell1) {
                    if ($k <= 0) {
                        $spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1 - 1), $cell1[1]);
                        //Set Font
                        $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
                        $spreadsheet->getDefaultStyle()->getFont()->setName("Arial");
                        $spreadsheet->getActiveSheet()->getStyle($key1 . ($index1 - 1))->getFont()->setBold(true);
                    }
                    //$spreadsheet->getActiveSheet()->getColumnDimension($key1)->setAutoSize(true);
                    //$spreadsheet->getActiveSheet()->getColumnDimension($key1)->setWidth(20);
                    //$spreadsheet->getActiveSheet()->getStyle($key1 . ($index1))->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                    if (is_object($tableData)) {
                        try {
                            $spreadsheet->getActiveSheet()->setCellValueExplicit($key1 . ($index1), $item->{$cell1[0]}(), DataType::TYPE_STRING);
                            //$spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1), $item->{$cell1[0]}()); // Custom Attribute function
                        } catch (Exception $e) {
                            $spreadsheet->getActiveSheet()->setCellValueExplicit($key1 . ($index1), $item->{$cell1[0]}, DataType::TYPE_STRING);
                            //$spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1), $item->{$cell1[0]});
                        }
                    } else {
                        // 解决长数字串显示为科学计数（1.23E+12）
                        $spreadsheet->getActiveSheet()->setCellValueExplicit($key1 . ($index1), $item[$cell1[0]], DataType::TYPE_STRING);
                        //$spreadsheet->getActiveSheet()->setCellValue($key1 . ($index1), $item[$cell1[0]]);
                    }
                }
                // 每执行1000条数据
                if ($index1 % 1000 == 0) {
                    sleep(1);
                }
                $index1++;
            }

            // 检查文件或目录是否存在
            if (!file_exists($filePath)) {
                mkdir($filePath, 0777, true);
            }
            $writer = new Xlsx($spreadsheet);
            $suffix = '.xlsx'; // .xls
            $writer->save($fileName . $suffix); // or .xlsx

            $newFileName = substr($fileName, strripos($fileName, 'uploads/') + 8);
            $newFileName = full_file_url($newFileName);
            $fileUrl = $newFileName . $suffix;
            if ($isDownload) {
                /*$xlsTitle = iconv('utf-8', 'gb2312', $xlsTitle); // 文件名称
                $downFileName = basename($fileName, '.' . pathinfo($fileName, PATHINFO_EXTENSION));
                header('pragma:public');
                header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
                header("Content-Disposition:attachment;filename=$downFileName.xls"); // attachment 新窗口打印,inline 本窗口打印

                $writer->save('php://output');*/
                //exit;

                header('location:' . $fileUrl);
                exit;
            } else {
                return $fileUrl;
            }
            //sleep(1);
        }
    }
}
